﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Data.OleDb;

/// <summary>
///DBControl 的摘要说明
/// </summary>
public class DBControl
{
    //DB为SqlServer
    //static string connStr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

    //DB为Access
    static string connStr = ConfigurationManager.ConnectionStrings["AccessConn"].ConnectionString + HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["AccessPath"].ConnectionString);
    static OleDbConnection conn;

    public DBControl()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>
    /// 查询数据库
    /// </summary>
    /// <param name="sqlStr"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static DataSet Search(string sqlStr, Dictionary<string, string> param)
    {
        DataSet ds = new DataSet();
        using (conn = new OleDbConnection(connStr))
        {
            OleDbCommand cmd = new OleDbCommand(sqlStr, conn);
            if (param != null && param.Count > 0)
            {
                foreach (var item in param)
                {
                    cmd.Parameters.AddWithValue(item.Key, item.Value);
                }
            }
            try
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds, "Result");
            }
            catch(Exception e) 
            {
                Console.WriteLine(e.Message);
            }
        }
        return ds;
    }
    /// <summary>
    /// 操作数据库
    /// </summary>
    /// <param name="sqlStr"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static int Operate(string sqlStr, Dictionary<string, string> param)
    {
        int result = 0;

        using (conn = new OleDbConnection(connStr))
        {
            
            OleDbCommand cmd = new OleDbCommand(sqlStr, conn);
            if (param != null && param.Count > 0)
            {
                foreach (var item in param)
                {
                    cmd.Parameters.AddWithValue(item.Key, item.Value);
                }
            }
            try
            {
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e) 
            {
                Console.WriteLine(e.Message);
            }
        }
        return result;
    }
}